{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "#pip install polars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (5,)\n",
      "Series: 'ints' [i64]\n",
      "[\n",
      "\t1\n",
      "\t2\n",
      "\t3\n",
      "\t4\n",
      "\t5\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "s = pl.Series('ints',[1,2,3,4,5])\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 4)\n",
      "┌────────────────┬────────────┬────────┬────────┐\n",
      "│ name           ┆ birthdate  ┆ weight ┆ height │\n",
      "│ ---            ┆ ---        ┆ ---    ┆ ---    │\n",
      "│ str            ┆ date       ┆ f64    ┆ f64    │\n",
      "╞════════════════╪════════════╪════════╪════════╡\n",
      "│ Alice Archer   ┆ 1997-01-10 ┆ 57.9   ┆ 1.56   │\n",
      "│ Ben Brown      ┆ 1985-02-15 ┆ 72.5   ┆ 1.77   │\n",
      "│ Chloe Cooper   ┆ 1983-03-22 ┆ 53.6   ┆ 1.65   │\n",
      "│ Daniel Donovan ┆ 1981-04-30 ┆ 83.1   ┆ 1.75   │\n",
      "└────────────────┴────────────┴────────┴────────┘\n"
     ]
    }
   ],
   "source": [
    "from datetime import date\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"name\": [\"Alice Archer\", \"Ben Brown\", \"Chloe Cooper\", \"Daniel Donovan\"],\n",
    "        \"birthdate\": [\n",
    "            date(1997, 1, 10),\n",
    "            date(1985, 2, 15),\n",
    "            date(1983, 3, 22),\n",
    "            date(1981, 4, 30),\n",
    "        ],\n",
    "        \"weight\": [57.9, 72.5, 53.6, 83.1],  # (kg)\n",
    "        \"height\": [1.56, 1.77, 1.65, 1.75],  # (m)\n",
    "    }\n",
    ")\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(col(\"weight\")) / (col(\"height\").pow([dyn int: 2]))]\n"
     ]
    }
   ],
   "source": [
    "expression = pl.col('weight') / (pl.col('height') ** 2)\n",
    "print(expression)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 3)\n",
      "┌────────────────┬────────────┬───────────┐\n",
      "│ name           ┆ borth_year ┆ bmi       │\n",
      "│ ---            ┆ ---        ┆ ---       │\n",
      "│ str            ┆ i32        ┆ f64       │\n",
      "╞════════════════╪════════════╪═══════════╡\n",
      "│ Alice Archer   ┆ 1997       ┆ 23.791913 │\n",
      "│ Ben Brown      ┆ 1985       ┆ 23.141498 │\n",
      "│ Chloe Cooper   ┆ 1983       ┆ 19.687787 │\n",
      "│ Daniel Donovan ┆ 1981       ┆ 27.134694 │\n",
      "└────────────────┴────────────┴───────────┘\n"
     ]
    }
   ],
   "source": [
    "result = df.select(pl.col('name'),\n",
    "                   pl.col('birthdate').dt.year().alias('borth_year'),\n",
    "                   (pl.col('weight') / (pl.col('height') ** 2)).alias('bmi'),\n",
    "                   )\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 1)\n",
      "┌────────┐\n",
      "│ weight │\n",
      "│ ---    │\n",
      "│ f64    │\n",
      "╞════════╡\n",
      "│ 5.79   │\n",
      "│ 7.25   │\n",
      "│ 5.36   │\n",
      "│ 8.31   │\n",
      "└────────┘\n"
     ]
    }
   ],
   "source": [
    "result = df.select(pl.col('weight')/10)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 5)\n",
      "┌────────────────┬────────────┬────────┬────────┬──────┐\n",
      "│ name           ┆ birthdate  ┆ weight ┆ height ┆ year │\n",
      "│ ---            ┆ ---        ┆ ---    ┆ ---    ┆ ---  │\n",
      "│ str            ┆ date       ┆ f64    ┆ f64    ┆ i32  │\n",
      "╞════════════════╪════════════╪════════╪════════╪══════╡\n",
      "│ Alice Archer   ┆ 1997-01-10 ┆ 57.9   ┆ 1.56   ┆ 1997 │\n",
      "│ Ben Brown      ┆ 1985-02-15 ┆ 72.5   ┆ 1.77   ┆ 1985 │\n",
      "│ Chloe Cooper   ┆ 1983-03-22 ┆ 53.6   ┆ 1.65   ┆ 1983 │\n",
      "│ Daniel Donovan ┆ 1981-04-30 ┆ 83.1   ┆ 1.75   ┆ 1981 │\n",
      "└────────────────┴────────────┴────────┴────────┴──────┘\n"
     ]
    }
   ],
   "source": [
    "df1 = df.clone()\n",
    "df1 = df1.with_columns(pl.col('birthdate').dt.year().alias('year'))\n",
    "print(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 5)\n",
      "┌────────────────┬───────────┬────────┬────────┬──────┐\n",
      "│ name           ┆ birthdate ┆ weight ┆ height ┆ year │\n",
      "│ ---            ┆ ---       ┆ ---    ┆ ---    ┆ ---  │\n",
      "│ str            ┆ i32       ┆ f64    ┆ f64    ┆ i32  │\n",
      "╞════════════════╪═══════════╪════════╪════════╪══════╡\n",
      "│ Alice Archer   ┆ 1997      ┆ 57.9   ┆ 1.56   ┆ 1997 │\n",
      "│ Ben Brown      ┆ 1985      ┆ 72.5   ┆ 1.77   ┆ 1985 │\n",
      "│ Chloe Cooper   ┆ 1983      ┆ 53.6   ┆ 1.65   ┆ 1983 │\n",
      "│ Daniel Donovan ┆ 1981      ┆ 83.1   ┆ 1.75   ┆ 1981 │\n",
      "└────────────────┴───────────┴────────┴────────┴──────┘\n"
     ]
    }
   ],
   "source": [
    "df1 = df1.with_columns(pl.col('birthdate').dt.year())\n",
    "print(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (1, 4)\n",
      "┌───────────┬────────────┬────────┬────────┐\n",
      "│ name      ┆ birthdate  ┆ weight ┆ height │\n",
      "│ ---       ┆ ---        ┆ ---    ┆ ---    │\n",
      "│ str       ┆ date       ┆ f64    ┆ f64    │\n",
      "╞═══════════╪════════════╪════════╪════════╡\n",
      "│ Ben Brown ┆ 1985-02-15 ┆ 72.5   ┆ 1.77   │\n",
      "└───────────┴────────────┴────────┴────────┘\n"
     ]
    }
   ],
   "source": [
    "result = df.filter(\n",
    "    (pl.col('birthdate').is_between(date(1982,12,31),date(1996,1,1))) & \n",
    "    (pl.col('height') > 1.7)\n",
    ")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 4)\n",
      "┌────────────────┬────────────┬────────┬────────┐\n",
      "│ name           ┆ birthdate  ┆ weight ┆ height │\n",
      "│ ---            ┆ ---        ┆ ---    ┆ ---    │\n",
      "│ str            ┆ date       ┆ f64    ┆ f64    │\n",
      "╞════════════════╪════════════╪════════╪════════╡\n",
      "│ Ben Brown      ┆ 1985-02-15 ┆ 72.5   ┆ 1.77   │\n",
      "│ Chloe Cooper   ┆ 1983-03-22 ┆ 53.6   ┆ 1.65   │\n",
      "│ Daniel Donovan ┆ 1981-04-30 ┆ 83.1   ┆ 1.75   │\n",
      "└────────────────┴────────────┴────────┴────────┘\n"
     ]
    }
   ],
   "source": [
    "result = df.filter(\n",
    "    (pl.col('birthdate').is_between(date(1982,12,31),date(1996,1,1))) | \n",
    "    (pl.col('height') > 1.7)\n",
    ")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (2, 4)\n",
      "┌────────┬─────────────────────────────────┬───────────┬───────────┐\n",
      "│ decade ┆ name                            ┆ weight    ┆ birthdate │\n",
      "│ ---    ┆ ---                             ┆ ---       ┆ ---       │\n",
      "│ i32    ┆ list[str]                       ┆ f64       ┆ u32       │\n",
      "╞════════╪═════════════════════════════════╪═══════════╪═══════════╡\n",
      "│ 1980   ┆ [\"Ben Brown\", \"Chloe Cooper\", … ┆ 69.733333 ┆ 3         │\n",
      "│ 1990   ┆ [\"Alice Archer\"]                ┆ 57.9      ┆ 1         │\n",
      "└────────┴─────────────────────────────────┴───────────┴───────────┘\n"
     ]
    }
   ],
   "source": [
    "result = df.group_by(\n",
    "    (pl.col('birthdate').dt.year() // 10 * 10).alias('decade')\n",
    ").agg(\n",
    "    pl.col('name'),\n",
    "    pl.col('weight').mean(),\n",
    "    pl.col('birthdate').count()\n",
    ")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pl.scan_csv(\"D:\\\\Py_work_space\\\\save_data\\\\flights.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 20)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th></th><th>year</th><th>month</th><th>day</th><th>dep_time</th><th>sched_dep_time</th><th>dep_delay</th><th>arr_time</th><th>sched_arr_time</th><th>arr_delay</th><th>carrier</th><th>flight</th><th>tailnum</th><th>origin</th><th>dest</th><th>air_time</th><th>distance</th><th>hour</th><th>minute</th><th>time_hour</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td><td>i64</td><td>f64</td><td>f64</td><td>i64</td><td>f64</td><td>str</td><td>i64</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>i64</td><td>i64</td><td>i64</td><td>str</td></tr></thead><tbody><tr><td>0</td><td>2013</td><td>1</td><td>1</td><td>517.0</td><td>515</td><td>2.0</td><td>830.0</td><td>819</td><td>11.0</td><td>&quot;UA&quot;</td><td>1545</td><td>&quot;N14228&quot;</td><td>&quot;EWR&quot;</td><td>&quot;IAH&quot;</td><td>227.0</td><td>1400</td><td>5</td><td>15</td><td>&quot;2013-01-01T10:00:00Z&quot;</td></tr><tr><td>1</td><td>2013</td><td>1</td><td>1</td><td>533.0</td><td>529</td><td>4.0</td><td>850.0</td><td>830</td><td>20.0</td><td>&quot;UA&quot;</td><td>1714</td><td>&quot;N24211&quot;</td><td>&quot;LGA&quot;</td><td>&quot;IAH&quot;</td><td>227.0</td><td>1416</td><td>5</td><td>29</td><td>&quot;2013-01-01T10:00:00Z&quot;</td></tr><tr><td>2</td><td>2013</td><td>1</td><td>1</td><td>542.0</td><td>540</td><td>2.0</td><td>923.0</td><td>850</td><td>33.0</td><td>&quot;AA&quot;</td><td>1141</td><td>&quot;N619AA&quot;</td><td>&quot;JFK&quot;</td><td>&quot;MIA&quot;</td><td>160.0</td><td>1089</td><td>5</td><td>40</td><td>&quot;2013-01-01T10:00:00Z&quot;</td></tr><tr><td>3</td><td>2013</td><td>1</td><td>1</td><td>544.0</td><td>545</td><td>-1.0</td><td>1004.0</td><td>1022</td><td>-18.0</td><td>&quot;B6&quot;</td><td>725</td><td>&quot;N804JB&quot;</td><td>&quot;JFK&quot;</td><td>&quot;BQN&quot;</td><td>183.0</td><td>1576</td><td>5</td><td>45</td><td>&quot;2013-01-01T10:00:00Z&quot;</td></tr><tr><td>4</td><td>2013</td><td>1</td><td>1</td><td>554.0</td><td>600</td><td>-6.0</td><td>812.0</td><td>837</td><td>-25.0</td><td>&quot;DL&quot;</td><td>461</td><td>&quot;N668DN&quot;</td><td>&quot;LGA&quot;</td><td>&quot;ATL&quot;</td><td>116.0</td><td>762</td><td>6</td><td>0</td><td>&quot;2013-01-01T11:00:00Z&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 20)\n",
       "┌─────┬──────┬───────┬─────┬───┬──────────┬──────┬────────┬──────────────────────┐\n",
       "│     ┆ year ┆ month ┆ day ┆ … ┆ distance ┆ hour ┆ minute ┆ time_hour            │\n",
       "│ --- ┆ ---  ┆ ---   ┆ --- ┆   ┆ ---      ┆ ---  ┆ ---    ┆ ---                  │\n",
       "│ i64 ┆ i64  ┆ i64   ┆ i64 ┆   ┆ i64      ┆ i64  ┆ i64    ┆ str                  │\n",
       "╞═════╪══════╪═══════╪═════╪═══╪══════════╪══════╪════════╪══════════════════════╡\n",
       "│ 0   ┆ 2013 ┆ 1     ┆ 1   ┆ … ┆ 1400     ┆ 5    ┆ 15     ┆ 2013-01-01T10:00:00Z │\n",
       "│ 1   ┆ 2013 ┆ 1     ┆ 1   ┆ … ┆ 1416     ┆ 5    ┆ 29     ┆ 2013-01-01T10:00:00Z │\n",
       "│ 2   ┆ 2013 ┆ 1     ┆ 1   ┆ … ┆ 1089     ┆ 5    ┆ 40     ┆ 2013-01-01T10:00:00Z │\n",
       "│ 3   ┆ 2013 ┆ 1     ┆ 1   ┆ … ┆ 1576     ┆ 5    ┆ 45     ┆ 2013-01-01T10:00:00Z │\n",
       "│ 4   ┆ 2013 ┆ 1     ┆ 1   ┆ … ┆ 762      ┆ 6    ┆ 0      ┆ 2013-01-01T11:00:00Z │\n",
       "└─────┴──────┴───────┴─────┴───┴──────────┴──────┴────────┴──────────────────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.head().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = flights.filter(pl.col('dep_time') > 500).group_by([\"dest\",\"carrier\"]).agg(\n",
    "    pl.col('sched_arr_time').mean(),\n",
    "    pl.col('tailnum').count()\n",
    "\n",
    "\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (313, 4)\n",
      "┌──────┬─────────┬────────────────┬─────────┐\n",
      "│ dest ┆ carrier ┆ sched_arr_time ┆ tailnum │\n",
      "│ ---  ┆ ---     ┆ ---            ┆ ---     │\n",
      "│ str  ┆ str     ┆ f64            ┆ u32     │\n",
      "╞══════╪═════════╪════════════════╪═════════╡\n",
      "│ RSW  ┆ B6      ┆ 1442.832314    ┆ 1962    │\n",
      "│ CRW  ┆ MQ      ┆ 2030.0         ┆ 137     │\n",
      "│ MEM  ┆ EV      ┆ 1409.337017    ┆ 1267    │\n",
      "│ ORF  ┆ EV      ┆ 1524.113889    ┆ 720     │\n",
      "│ CAE  ┆ EV      ┆ 2021.375       ┆ 104     │\n",
      "│ …    ┆ …       ┆ …              ┆ …       │\n",
      "│ MHT  ┆ EV      ┆ 1597.932893    ┆ 909     │\n",
      "│ PHX  ┆ WN      ┆ 1557.018779    ┆ 426     │\n",
      "│ TPA  ┆ AA      ┆ 1841.50974     ┆ 308     │\n",
      "│ ORF  ┆ MQ      ┆ 1832.066474    ┆ 346     │\n",
      "│ CLE  ┆ OO      ┆ 1953.0         ┆ 21      │\n",
      "└──────┴─────────┴────────────────┴─────────┘\n"
     ]
    }
   ],
   "source": [
    "result1 = result.collect()\n",
    "print(result1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'d:\\\\Py_work_space'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1982-12-31\n"
     ]
    }
   ],
   "source": [
    "print(date(1982,12,31))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 7)\n",
      "┌───────┬─────┬────────┬────────────┬───────────┬────────────┬─────────────────────┐\n",
      "│ index ┆ id  ┆ place  ┆ date       ┆ sales     ┆ has_people ┆ logged_at           │\n",
      "│ ---   ┆ --- ┆ ---    ┆ ---        ┆ ---       ┆ ---        ┆ ---                 │\n",
      "│ u32   ┆ i64 ┆ str    ┆ date       ┆ f64       ┆ bool       ┆ datetime[μs]        │\n",
      "╞═══════╪═════╪════════╪════════════╪═══════════╪════════════╪═════════════════════╡\n",
      "│ 0     ┆ 9   ┆ Mars   ┆ 2022-01-01 ┆ 33.4      ┆ false      ┆ 2022-12-01 00:00:00 │\n",
      "│ 1     ┆ 4   ┆ Earth  ┆ 2022-01-02 ┆ 2142134.1 ┆ true       ┆ 2022-12-01 00:00:01 │\n",
      "│ 2     ┆ 2   ┆ Saturn ┆ 2022-01-03 ┆ 44.7      ┆ false      ┆ 2022-12-01 00:00:02 │\n",
      "└───────┴─────┴────────┴────────────┴───────────┴────────────┴─────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#初始数据\n",
    "from datetime import date, datetime\n",
    "\n",
    "import polars as pl\n",
    "\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"id\": [9, 4, 2],\n",
    "        \"place\": [\"Mars\", \"Earth\", \"Saturn\"],\n",
    "        \"date\": pl.date_range(date(2022, 1, 1), date(2022, 1, 3), \"1d\", eager=True),\n",
    "        \"sales\": [33.4, 2142134.1, 44.7],\n",
    "        \"has_people\": [False, True, False],\n",
    "        \"logged_at\": pl.datetime_range(\n",
    "            datetime(2022, 12, 1), datetime(2022, 12, 1, 0, 0, 2), \"1s\", eager=True\n",
    "        ),\n",
    "    }\n",
    ").with_row_index(\"index\")\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 7)\n",
      "┌───────┬─────┬────────┬────────────┬───────────┬────────────┬─────────────────────┐\n",
      "│ index ┆ id  ┆ place  ┆ date       ┆ sales     ┆ has_people ┆ logged_at           │\n",
      "│ ---   ┆ --- ┆ ---    ┆ ---        ┆ ---       ┆ ---        ┆ ---                 │\n",
      "│ u32   ┆ i64 ┆ str    ┆ date       ┆ f64       ┆ bool       ┆ datetime[μs]        │\n",
      "╞═══════╪═════╪════════╪════════════╪═══════════╪════════════╪═════════════════════╡\n",
      "│ 0     ┆ 9   ┆ Mars   ┆ 2022-01-01 ┆ 33.4      ┆ false      ┆ 2022-12-01 00:00:00 │\n",
      "│ 1     ┆ 4   ┆ Earth  ┆ 2022-01-02 ┆ 2142134.1 ┆ true       ┆ 2022-12-01 00:00:01 │\n",
      "│ 2     ┆ 2   ┆ Saturn ┆ 2022-01-03 ┆ 44.7      ┆ false      ┆ 2022-12-01 00:00:02 │\n",
      "└───────┴─────┴────────┴────────────┴───────────┴────────────┴─────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "out = df.select(pl.col('*'))\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 7)\n",
      "┌───────┬─────┬────────┬────────────┬───────────┬────────────┬─────────────────────┐\n",
      "│ index ┆ id  ┆ place  ┆ date       ┆ sales     ┆ has_people ┆ logged_at           │\n",
      "│ ---   ┆ --- ┆ ---    ┆ ---        ┆ ---       ┆ ---        ┆ ---                 │\n",
      "│ u32   ┆ i64 ┆ str    ┆ date       ┆ f64       ┆ bool       ┆ datetime[μs]        │\n",
      "╞═══════╪═════╪════════╪════════════╪═══════════╪════════════╪═════════════════════╡\n",
      "│ 0     ┆ 9   ┆ Mars   ┆ 2022-01-01 ┆ 33.4      ┆ false      ┆ 2022-12-01 00:00:00 │\n",
      "│ 1     ┆ 4   ┆ Earth  ┆ 2022-01-02 ┆ 2142134.1 ┆ true       ┆ 2022-12-01 00:00:01 │\n",
      "│ 2     ┆ 2   ┆ Saturn ┆ 2022-01-03 ┆ 44.7      ┆ false      ┆ 2022-12-01 00:00:02 │\n",
      "└───────┴─────┴────────┴────────────┴───────────┴────────────┴─────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "out = df.select(pl.all())\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 5)\n",
      "┌─────┬────────┬────────────┬───────────┬────────────┐\n",
      "│ id  ┆ place  ┆ date       ┆ sales     ┆ has_people │\n",
      "│ --- ┆ ---    ┆ ---        ┆ ---       ┆ ---        │\n",
      "│ i64 ┆ str    ┆ date       ┆ f64       ┆ bool       │\n",
      "╞═════╪════════╪════════════╪═══════════╪════════════╡\n",
      "│ 9   ┆ Mars   ┆ 2022-01-01 ┆ 33.4      ┆ false      │\n",
      "│ 4   ┆ Earth  ┆ 2022-01-02 ┆ 2142134.1 ┆ true       │\n",
      "│ 2   ┆ Saturn ┆ 2022-01-03 ┆ 44.7      ┆ false      │\n",
      "└─────┴────────┴────────────┴───────────┴────────────┘\n"
     ]
    }
   ],
   "source": [
    "out = df.select(pl.all().exclude(\"logged_at\",\"index\"))\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 2)\n",
      "┌───────────┬────────────┐\n",
      "│ sales     ┆ has_people │\n",
      "│ ---       ┆ ---        │\n",
      "│ f64       ┆ bool       │\n",
      "╞═══════════╪════════════╡\n",
      "│ 33.4      ┆ false      │\n",
      "│ 2142134.1 ┆ true       │\n",
      "│ 44.7      ┆ false      │\n",
      "└───────────┴────────────┘\n"
     ]
    }
   ],
   "source": [
    "#用正则\n",
    "out = df.select(pl.col(\"^.*(as|sa).*$\")) #选名字中有as或sa的列，这个知到就行，没必要记住感觉用不到\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (1, 3)\n",
      "┌───────┬─────┬────────────┐\n",
      "│ index ┆ id  ┆ has_people │\n",
      "│ ---   ┆ --- ┆ ---        │\n",
      "│ u32   ┆ u32 ┆ u32        │\n",
      "╞═══════╪═════╪════════════╡\n",
      "│ 3     ┆ 3   ┆ 2          │\n",
      "└───────┴─────┴────────────┘\n"
     ]
    }
   ],
   "source": [
    "#通过数据类型\n",
    "out = df.select(pl.col(pl.Int64, pl.UInt32, pl.Boolean).n_unique()) #nunique找每列中不同值的数量，记住\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 3)\n",
      "┌───────┬─────┬────────────┐\n",
      "│ index ┆ id  ┆ has_people │\n",
      "│ ---   ┆ --- ┆ ---        │\n",
      "│ u32   ┆ i64 ┆ bool       │\n",
      "╞═══════╪═════╪════════════╡\n",
      "│ 0     ┆ 9   ┆ false      │\n",
      "│ 1     ┆ 4   ┆ true       │\n",
      "│ 2     ┆ 2   ┆ false      │\n",
      "└───────┴─────┴────────────┘\n"
     ]
    }
   ],
   "source": [
    "#通过数据类型\n",
    "out = df.select(pl.col(pl.Int64, pl.UInt32, pl.Boolean)) #nunique找每列中不同值的数量，记住\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 3)\n",
      "┌───────┬─────┬────────┐\n",
      "│ index ┆ id  ┆ place  │\n",
      "│ ---   ┆ --- ┆ ---    │\n",
      "│ u32   ┆ i64 ┆ str    │\n",
      "╞═══════╪═════╪════════╡\n",
      "│ 0     ┆ 9   ┆ Mars   │\n",
      "│ 1     ┆ 4   ┆ Earth  │\n",
      "│ 2     ┆ 2   ┆ Saturn │\n",
      "└───────┴─────┴────────┘\n",
      "shape: (3, 2)\n",
      "┌─────┬───────────┐\n",
      "│ id  ┆ sales     │\n",
      "│ --- ┆ ---       │\n",
      "│ i64 ┆ f64       │\n",
      "╞═════╪═══════════╡\n",
      "│ 9   ┆ 33.4      │\n",
      "│ 4   ┆ 2142134.1 │\n",
      "│ 2   ┆ 44.7      │\n",
      "└─────┴───────────┘\n",
      "shape: (3, 5)\n",
      "┌───────┬────────┬────────────┬────────────┬─────────────────────┐\n",
      "│ index ┆ place  ┆ date       ┆ has_people ┆ logged_at           │\n",
      "│ ---   ┆ ---    ┆ ---        ┆ ---        ┆ ---                 │\n",
      "│ u32   ┆ str    ┆ date       ┆ bool       ┆ datetime[μs]        │\n",
      "╞═══════╪════════╪════════════╪════════════╪═════════════════════╡\n",
      "│ 0     ┆ Mars   ┆ 2022-01-01 ┆ false      ┆ 2022-12-01 00:00:00 │\n",
      "│ 1     ┆ Earth  ┆ 2022-01-02 ┆ true       ┆ 2022-12-01 00:00:01 │\n",
      "│ 2     ┆ Saturn ┆ 2022-01-03 ┆ false      ┆ 2022-12-01 00:00:02 │\n",
      "└───────┴────────┴────────────┴────────────┴─────────────────────┘\n",
      "shape: (3, 3)\n",
      "┌───────┬────────────┬─────────────────────┐\n",
      "│ index ┆ has_people ┆ logged_at           │\n",
      "│ ---   ┆ ---        ┆ ---                 │\n",
      "│ u32   ┆ bool       ┆ datetime[μs]        │\n",
      "╞═══════╪════════════╪═════════════════════╡\n",
      "│ 0     ┆ false      ┆ 2022-12-01 00:00:00 │\n",
      "│ 1     ┆ true       ┆ 2022-12-01 00:00:01 │\n",
      "│ 2     ┆ false      ┆ 2022-12-01 00:00:02 │\n",
      "└───────┴────────────┴─────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#用selector这个感觉没什么用了解即可\n",
    "import polars.selectors as cs  #\n",
    "\n",
    "out = df.select(cs.integer(), cs.string())\n",
    "print(out)\n",
    "\n",
    "out = df.select(cs.numeric() - cs.first())  #选数值类型的列，并且这些列的每个值要减去列的本第一个值\n",
    "print(out)\n",
    "\n",
    "out = df.select(cs.by_name(\"index\") | ~cs.numeric())  #选名字是‘index’的列和不是数值类型的列\n",
    "print(out)\n",
    "\n",
    "out = df.select(cs.contains(\"index\"), cs.matches(\".*_.*\")) #选名字包含‘index’的列和名字内有‘_’的列\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (5, 2)\n",
      "┌──────┬─────────────┐\n",
      "│ nrs  ┆ conditional │\n",
      "│ ---  ┆ ---         │\n",
      "│ i64  ┆ bool        │\n",
      "╞══════╪═════════════╡\n",
      "│ 1    ┆ false       │\n",
      "│ 2    ┆ false       │\n",
      "│ 3    ┆ true        │\n",
      "│ null ┆ false       │\n",
      "│ 5    ┆ true        │\n",
      "└──────┴─────────────┘\n"
     ]
    }
   ],
   "source": [
    "#这个还挺常用的，要记住\n",
    "import numpy as np\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"nrs\": [1, 2, 3, None, 5],\n",
    "        \"names\": [\"foo\", \"ham\", \"spam\", \"egg\", \"spam\"],\n",
    "        \"random\": np.random.rand(5),\n",
    "        \"groups\": [\"A\", \"A\", \"B\", \"C\", \"B\"],\n",
    "    }\n",
    ")\n",
    "df_conditional = df.select(\n",
    "    pl.col(\"nrs\"),\n",
    "    pl.when(pl.col(\"nrs\") > 2)\n",
    "    .then(True)\n",
    "    .otherwise(pl.lit(False))\n",
    "    .alias(\"conditional\"),\n",
    ")\n",
    "print(df_conditional)\n",
    "\n",
    "#pl.lit是将输入数据转化为文本的函数，不过没什么用，不加的话还没发现有什么影响"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (5, 3)\n",
      "┌────────────────────┬────────────────────┬─────────────────────────────────┐\n",
      "│ integers_as_floats ┆ floats_as_integers ┆ floats_with_decimal_as_integer… │\n",
      "│ ---                ┆ ---                ┆ ---                             │\n",
      "│ f32                ┆ i32                ┆ i32                             │\n",
      "╞════════════════════╪════════════════════╪═════════════════════════════════╡\n",
      "│ 1.0                ┆ 4                  ┆ 4                               │\n",
      "│ 2.0                ┆ 5                  ┆ 5                               │\n",
      "│ 3.0                ┆ 6                  ┆ 6                               │\n",
      "│ 4.0                ┆ 7                  ┆ 7                               │\n",
      "│ 5.0                ┆ 8                  ┆ 8                               │\n",
      "└────────────────────┴────────────────────┴─────────────────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#初始数据\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"integers\": [1, 2, 3, 4, 5],\n",
    "        \"big_integers\": [1, 10000002, 3, 10000004, 10000005],\n",
    "        \"floats\": [4.0, 5.0, 6.0, 7.0, 8.0],\n",
    "        \"floats_with_decimal\": [4.532, 5.5, 6.5, 7.5, 8.5],\n",
    "    }\n",
    ")\n",
    "\n",
    "#一般用cast\n",
    "out = df.select(\n",
    "    pl.col(\"integers\").cast(pl.Float32).alias(\"integers_as_floats\"),\n",
    "    pl.col(\"floats\").cast(pl.Int32).alias(\"floats_as_integers\"),\n",
    "    pl.col(\"floats_with_decimal\")\n",
    "    .cast(pl.Int32)\n",
    "    .alias(\"floats_with_decimal_as_integers\"),\n",
    ")\n",
    "print(out)  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (5, 2)\n",
      "┌────────────┬─────────────────────┐\n",
      "│ date       ┆ string              │\n",
      "│ ---        ┆ ---                 │\n",
      "│ str        ┆ datetime[μs]        │\n",
      "╞════════════╪═════════════════════╡\n",
      "│ 2022-01-01 ┆ 2022-01-01 00:00:00 │\n",
      "│ 2022-01-02 ┆ 2022-01-02 00:00:00 │\n",
      "│ 2022-01-03 ┆ 2022-01-03 00:00:00 │\n",
      "│ 2022-01-04 ┆ 2022-01-04 00:00:00 │\n",
      "│ 2022-01-05 ┆ 2022-01-05 00:00:00 │\n",
      "└────────────┴─────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#对于str转日期类型或者日期转str类型有以下两种方法\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"date\": pl.date_range(date(2022, 1, 1), date(2022, 1, 5), eager=True),\n",
    "        \"string\": [\n",
    "            \"2022-01-01\",\n",
    "            \"2022-01-02\",\n",
    "            \"2022-01-03\",\n",
    "            \"2022-01-04\",\n",
    "            \"2022-01-05\",\n",
    "        ],\n",
    "    }\n",
    ")\n",
    "out = df.select(\n",
    "    pl.col(\"date\").dt.to_string(\"%Y-%m-%d\"),\n",
    "    pl.col(\"string\").str.to_datetime(\"%Y-%m-%d\"),\n",
    ")\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 1)\n",
      "┌─────────────┐\n",
      "│ animal      │\n",
      "│ ---         │\n",
      "│ str         │\n",
      "╞═════════════╡\n",
      "│ Crab        │\n",
      "│ cat and dog │\n",
      "│ rab$bit     │\n",
      "│ null        │\n",
      "└─────────────┘\n",
      "shape: (4, 2)\n",
      "┌────────────┬──────────────┐\n",
      "│ byte_count ┆ letter_count │\n",
      "│ ---        ┆ ---          │\n",
      "│ u32        ┆ u32          │\n",
      "╞════════════╪══════════════╡\n",
      "│ 4          ┆ 4            │\n",
      "│ 11         ┆ 11           │\n",
      "│ 7          ┆ 7            │\n",
      "│ null       ┆ null         │\n",
      "└────────────┴──────────────┘\n"
     ]
    }
   ],
   "source": [
    "#这里要用到str 的API先了解，后面的进阶篇会详细教\n",
    "df = pl.DataFrame({\"animal\": [\"Crab\", \"cat and dog\", \"rab$bit\", None]})\n",
    "print(df)\n",
    "#求长度\n",
    "out = df.select(\n",
    "    pl.col(\"animal\").str.len_bytes().alias(\"byte_count\"),\n",
    "    pl.col(\"animal\").str.len_chars().alias(\"letter_count\"),\n",
    ")\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (4, 4)\n",
      "┌───────┬─────────┬─────────────┬───────────┐\n",
      "│ regex ┆ literal ┆ starts_with ┆ ends_with │\n",
      "│ ---   ┆ ---     ┆ ---         ┆ ---       │\n",
      "│ bool  ┆ bool    ┆ bool        ┆ bool      │\n",
      "╞═══════╪═════════╪═════════════╪═══════════╡\n",
      "│ false ┆ false   ┆ false       ┆ false     │\n",
      "│ true  ┆ false   ┆ false       ┆ true      │\n",
      "│ true  ┆ true    ┆ true        ┆ false     │\n",
      "│ null  ┆ null    ┆ null        ┆ null      │\n",
      "└───────┴─────────┴─────────────┴───────────┘\n"
     ]
    }
   ],
   "source": [
    "#筛选符合str条件的行\n",
    "out = df.select(\n",
    "    pl.col(\"animal\").str.contains(\"cat|bit\").alias(\"regex\"),   #contain()，列的值中是否包含指定str\n",
    "    pl.col(\"animal\").str.contains(\"rab$\", literal=True).alias(\"literal\"),\n",
    "    pl.col(\"animal\").str.starts_with(\"rab\").alias(\"starts_with\"),   #starts_with()，是否以指定str开头\n",
    "    pl.col(\"animal\").str.ends_with(\"dog\").alias(\"ends_with\"),   #starts_with()，是否以指定str结尾\n",
    ")\n",
    "print(out)   #这里select出来的是布尔值将select改成filter即可实现筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (3, 1)\n",
      "┌─────────┐\n",
      "│ a       │\n",
      "│ ---     │\n",
      "│ str     │\n",
      "╞═════════╡\n",
      "│ messi   │\n",
      "│ null    │\n",
      "│ ronaldo │\n",
      "└─────────┘\n",
      "shape: (2, 1)\n",
      "┌────────────────┐\n",
      "│ extracted_nrs  │\n",
      "│ ---            │\n",
      "│ list[str]      │\n",
      "╞════════════════╡\n",
      "│ [\"123\", \"45\"]  │\n",
      "│ [\"678\", \"910\"] │\n",
      "└────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#提取指定的数值\n",
    "df = pl.DataFrame(\n",
    "    {\n",
    "        \"a\": [\n",
    "            \"http://vote.com/ballon_dor?candidate=messi&ref=polars\",\n",
    "            \"http://vote.com/ballon_dor?candidat=jorginho&ref=polars\",\n",
    "            \"http://vote.com/ballon_dor?candidate=ronaldo&ref=polars\",\n",
    "        ]\n",
    "    }\n",
    ")\n",
    "out = df.select(\n",
    "    pl.col(\"a\").str.extract(r\"candidate=(\\w+)\", group_index=1),  #用extract()提取‘candidate=’后的首组英文字母str\n",
    ")\n",
    "print(out)\n",
    "#提取指定的所有数值,并且生成List\n",
    "df = pl.DataFrame({\"foo\": [\"123 bla 45 asd\", \"xyz 678 910t\"]})\n",
    "out = df.select(\n",
    "    pl.col(\"foo\").str.extract_all(r\"(\\d+)\").alias(\"extracted_nrs\"),   #用extract_all()提取所有数字，并且生成List\n",
    ")\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (2, 2)\n",
      "┌─────┬────────┐\n",
      "│ id  ┆ text   │\n",
      "│ --- ┆ ---    │\n",
      "│ i64 ┆ str    │\n",
      "╞═════╪════════╡\n",
      "│ 1   ┆ 123abc │\n",
      "│ 2   ┆ abc456 │\n",
      "└─────┴────────┘\n",
      "shape: (2, 3)\n",
      "┌─────┬────────┬──────────────────┐\n",
      "│ id  ┆ text   ┆ text_replace_all │\n",
      "│ --- ┆ ---    ┆ ---              │\n",
      "│ i64 ┆ str    ┆ str              │\n",
      "╞═════╪════════╪══════════════════╡\n",
      "│ 1   ┆ 123abc ┆ 123-bc           │\n",
      "│ 2   ┆ abc456 ┆ -bc456           │\n",
      "└─────┴────────┴──────────────────┘\n"
     ]
    }
   ],
   "source": [
    "#替换值\n",
    "df = pl.DataFrame({\"id\": [1, 2], \"text\": [\"123abc\", \"abc456\"]})\n",
    "print(df)\n",
    "\n",
    "out = df.with_columns(\n",
    "    pl.col(\"text\").str.replace(r\"abc\", \"ABC\"),\n",
    "    pl.col(\"text\").str.replace_all(\"a\", \"-\", literal=True).alias(\"text_replace_all\"),\n",
    ")\n",
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enum得提前声明类别\n",
    "enum_dtype = pl.Enum([\"Polar\", \"Panda\", \"Brown\"])\n",
    "enum_series = pl.Series([\"Polar\", \"Panda\", \"Brown\", \"Brown\", \"Polar\"], dtype=enum_dtype)\n",
    "#Categorical不是\n",
    "cat_series = pl.Series(\n",
    "    [\"Polar\", \"Panda\", \"Brown\", \"Brown\", \"Polar\"], dtype=pl.Categorical\n",
    ")\n",
    "#一般用Categorical\n",
    "#类别数据的拼接很麻烦，因为程序不知道两个拼接数据的类别是否有不同比如有类别缺少或者类别增多。所以在拼接前建议先把类别数据转化成字符串类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (12_152, 36)\n",
      "┌───────────┬────────────┬────────────┬────────┬───┬────────────┬───────────┬──────────┬───────────┐\n",
      "│ last_name ┆ first_name ┆ middle_nam ┆ suffix ┆ … ┆ ballotpedi ┆ washingto ┆ icpsr_id ┆ wikipedia │\n",
      "│ ---       ┆ ---        ┆ e          ┆ ---    ┆   ┆ a_id       ┆ n_post_id ┆ ---      ┆ _id       │\n",
      "│ str       ┆ cat        ┆ ---        ┆ str    ┆   ┆ ---        ┆ ---       ┆ i64      ┆ ---       │\n",
      "│           ┆            ┆ str        ┆        ┆   ┆ str        ┆ str       ┆          ┆ str       │\n",
      "╞═══════════╪════════════╪════════════╪════════╪═══╪════════════╪═══════════╪══════════╪═══════════╡\n",
      "│ Bassett   ┆ Richard    ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 507      ┆ Richard   │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Bassett   │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ (Delaware │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ poli…     │\n",
      "│ Bland     ┆ Theodorick ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 786      ┆ Theodoric │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ k Bland   │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ (congress │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ man)      │\n",
      "│ Burke     ┆ Aedanus    ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 1260     ┆ Aedanus   │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Burke     │\n",
      "│ Carroll   ┆ Daniel     ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 1538     ┆ Daniel    │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Carroll   │\n",
      "│ Clymer    ┆ George     ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 1859     ┆ George    │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Clymer    │\n",
      "│ …         ┆ …          ┆ …          ┆ …      ┆ … ┆ …          ┆ …         ┆ …        ┆ …         │\n",
      "│ Menendez  ┆ Robert     ┆ null       ┆ null   ┆ … ┆ Bob        ┆ null      ┆ 29373    ┆ Bob       │\n",
      "│           ┆            ┆            ┆        ┆   ┆ Menendez   ┆           ┆          ┆ Menendez  │\n",
      "│ Gaetz     ┆ Matt       ┆ null       ┆ null   ┆ … ┆ Matt Gaetz ┆ null      ┆ 21719    ┆ Matt      │\n",
      "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Gaetz     │\n",
      "│ Butler    ┆ Laphonza   ┆ Romanique  ┆ null   ┆ … ┆ Laphonza   ┆ null      ┆ null     ┆ Laphonza  │\n",
      "│           ┆            ┆            ┆        ┆   ┆ Butler     ┆           ┆          ┆ Butler    │\n",
      "│ Helmy     ┆ George     ┆ S.         ┆ null   ┆ … ┆ George     ┆ null      ┆ null     ┆ George    │\n",
      "│           ┆            ┆            ┆        ┆   ┆ Helmy      ┆           ┆          ┆ Helmy     │\n",
      "│ Armstrong ┆ Kelly      ┆ null       ┆ null   ┆ … ┆ Kelly      ┆ null      ┆ 21901    ┆ Kelly     │\n",
      "│           ┆            ┆            ┆        ┆   ┆ Armstrong  ┆           ┆          ┆ Armstrong │\n",
      "└───────────┴────────────┴────────────┴────────┴───┴────────────┴───────────┴──────────┴───────────┘\n"
     ]
    }
   ],
   "source": [
    "#读取数据之前，预先定义各个列的数据类型\n",
    "url = \"https://theunitedstates.io/congress-legislators/legislators-historical.csv\"\n",
    "\n",
    "schema_overrides = {\n",
    "    \"first_name\": pl.Categorical,\n",
    "    \"gender\": pl.Categorical,\n",
    "    \"type\": pl.Categorical,\n",
    "    \"state\": pl.Categorical,\n",
    "    \"party\": pl.Categorical,\n",
    "}\n",
    "\n",
    "dataset = pl.read_csv(url, schema_overrides=schema_overrides).with_columns(\n",
    "    pl.col(\"birthday\").str.to_date(strict=False)\n",
    ")\n",
    "print(dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (3, 36)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>last_name</th><th>first_name</th><th>middle_name</th><th>suffix</th><th>nickname</th><th>full_name</th><th>birthday</th><th>gender</th><th>type</th><th>state</th><th>district</th><th>senate_class</th><th>party</th><th>url</th><th>address</th><th>phone</th><th>contact_form</th><th>rss_url</th><th>twitter</th><th>twitter_id</th><th>facebook</th><th>youtube</th><th>youtube_id</th><th>mastodon</th><th>bioguide_id</th><th>thomas_id</th><th>opensecrets_id</th><th>lis_id</th><th>fec_ids</th><th>cspan_id</th><th>govtrack_id</th><th>votesmart_id</th><th>ballotpedia_id</th><th>washington_post_id</th><th>icpsr_id</th><th>wikipedia_id</th></tr><tr><td>str</td><td>cat</td><td>str</td><td>str</td><td>str</td><td>str</td><td>date</td><td>cat</td><td>cat</td><td>cat</td><td>i64</td><td>i64</td><td>cat</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>i64</td><td>str</td><td>str</td><td>str</td><td>i64</td><td>str</td></tr></thead><tbody><tr><td>&quot;Bassett&quot;</td><td>&quot;Richard&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>1745-04-02</td><td>&quot;M&quot;</td><td>&quot;sen&quot;</td><td>&quot;DE&quot;</td><td>null</td><td>2</td><td>&quot;Anti-Administration&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;B000226&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>401222</td><td>null</td><td>null</td><td>null</td><td>507</td><td>&quot;Richard Bassett (Delaware poli…</td></tr><tr><td>&quot;Bland&quot;</td><td>&quot;Theodorick&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>1742-03-21</td><td>&quot;M&quot;</td><td>&quot;rep&quot;</td><td>&quot;VA&quot;</td><td>9</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;B000546&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>401521</td><td>null</td><td>null</td><td>null</td><td>786</td><td>&quot;Theodorick Bland (congressman)&quot;</td></tr><tr><td>&quot;Burke&quot;</td><td>&quot;Aedanus&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>1743-06-16</td><td>&quot;M&quot;</td><td>&quot;rep&quot;</td><td>&quot;SC&quot;</td><td>2</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;B001086&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>402032</td><td>null</td><td>null</td><td>null</td><td>1260</td><td>&quot;Aedanus Burke&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (3, 36)\n",
       "┌───────────┬────────────┬────────────┬────────┬───┬────────────┬───────────┬──────────┬───────────┐\n",
       "│ last_name ┆ first_name ┆ middle_nam ┆ suffix ┆ … ┆ ballotpedi ┆ washingto ┆ icpsr_id ┆ wikipedia │\n",
       "│ ---       ┆ ---        ┆ e          ┆ ---    ┆   ┆ a_id       ┆ n_post_id ┆ ---      ┆ _id       │\n",
       "│ str       ┆ cat        ┆ ---        ┆ str    ┆   ┆ ---        ┆ ---       ┆ i64      ┆ ---       │\n",
       "│           ┆            ┆ str        ┆        ┆   ┆ str        ┆ str       ┆          ┆ str       │\n",
       "╞═══════════╪════════════╪════════════╪════════╪═══╪════════════╪═══════════╪══════════╪═══════════╡\n",
       "│ Bassett   ┆ Richard    ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 507      ┆ Richard   │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Bassett   │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ (Delaware │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ poli…     │\n",
       "│ Bland     ┆ Theodorick ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 786      ┆ Theodoric │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ k Bland   │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ (congress │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ man)      │\n",
       "│ Burke     ┆ Aedanus    ┆ null       ┆ null   ┆ … ┆ null       ┆ null      ┆ 1260     ┆ Aedanus   │\n",
       "│           ┆            ┆            ┆        ┆   ┆            ┆           ┆          ┆ Burke     │\n",
       "└───────────┴────────────┴────────────┴────────┴───┴────────────┴───────────┴──────────┴───────────┘"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(dataset)\n",
    "dataset.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape: (5, 4)\n",
      "┌────────────┬──────┬───────────────────┬───────────┐\n",
      "│ first_name ┆ len  ┆ gender            ┆ last_name │\n",
      "│ ---        ┆ ---  ┆ ---               ┆ ---       │\n",
      "│ cat        ┆ u32  ┆ list[cat]         ┆ str       │\n",
      "╞════════════╪══════╪═══════════════════╪═══════════╡\n",
      "│ John       ┆ 1256 ┆ [\"M\", \"M\", … \"M\"] ┆ Walker    │\n",
      "│ William    ┆ 1022 ┆ [\"M\", \"M\", … \"M\"] ┆ Few       │\n",
      "│ James      ┆ 714  ┆ [\"M\", \"M\", … \"M\"] ┆ Armstrong │\n",
      "│ Thomas     ┆ 453  ┆ [\"M\", \"M\", … \"M\"] ┆ Tucker    │\n",
      "│ Charles    ┆ 439  ┆ [\"M\", \"M\", … \"M\"] ┆ Carroll   │\n",
      "└────────────┴──────┴───────────────────┴───────────┘\n"
     ]
    }
   ],
   "source": [
    "#用Lazyframe进行操作这个表加载时间比较久\n",
    "q = (\n",
    "    dataset.lazy()  #把Dataframe变成Lazyframe\n",
    "    .group_by(\"first_name\")   #按‘first_name’分组\n",
    "    .agg(\n",
    "        pl.len(),   #每组长度\n",
    "        pl.col(\"gender\"),   \n",
    "        pl.first(\"last_name\"),   #等于pl.col('last_name').first()，返回分组后该列每组的第一个值\n",
    "    )\n",
    "    .sort(\"len\", descending=True)  #按‘len’分组，降序排列\n",
    "    .limit(5)  #只显示前五行\n",
    ")\n",
    "\n",
    "df = q.collect()  #最后用collect把Lazyframe变成Dataframe\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
